﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MyGridView.BLL
{
    public class SqlHelper
    {
        private const string ConnStr = "Data Source=(local);Initial Catalog=CF;User id=sa;Password=1234;";
        private SqlConnection Conn = null;
        private SqlCommand Cmd = null;
        private SqlDataAdapter Adp = null;
        private DataSet ds = new DataSet();

        /// <summary>
        /// 返回 DataSet
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <param name="tableName"></param>
        public DataSet getDs(string cmdText, CommandType cmdType, SqlParameter[] pars, string tableName)
        {
            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            Adp = new SqlDataAdapter(Cmd);

            try
            {
                Conn.Open();
                if (tableName == null || tableName == string.Empty)
                    Adp.Fill(ds);
                else
                    Adp.Fill(ds, tableName);
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }


        /// <summary>
        /// 执行Sql语句，返回受影响的行数
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSql(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            int res = 0;
            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                res = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return res;
        }

        /// <summary>
        /// 返回 DataReader 对象
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public SqlDataReader getDr(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            SqlDataReader dr = null;

            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
            }
            return dr;
        }

        public object getScalar(string cmdText, CommandType cmdType, SqlParameter[] pars)
        {
            object res = null;

            Conn = new SqlConnection(ConnStr);
            Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = cmdText;
            Cmd.CommandType = cmdType;
            if (pars != null)
            {
                foreach (SqlParameter par in pars)
                {
                    Cmd.Parameters.Add(par);
                }
            }

            try
            {
                Conn.Open();
                res = Cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString());
            }
            finally
            {
                Conn.Close();
            }
            return res;
        }
        }
}
